package com.rad.datastat.database;

import java.util.ArrayList;
import java.util.concurrent.locks.ReentrantLock;

import com.rad.datastat.model.CallAndSmsInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class CallAndSmsDB {
	public static final String TABLE_NAME = "CallAndSms";

	public static final String COL_ID = "_id";
	public static final String COL_SMS_IN = "sms_in";
	public static final String COL_SMS_OUT = "sms_out";
	public static final String COL_CALL_IN = "call_in";
	public static final String COL_CALL_OUT = "call_out";
	public static final String COL_STATUS = "status"; // 0为未提交，1为已提交
	public static final String COL_DATE = "date"; // 2014-09-30

	private static CallAndSmsDB mInstance;
	private DBOpenHelper mDBOpenHelper;
	private ReentrantLock mLock;

	public CallAndSmsDB(Context context) {
		mDBOpenHelper = DBOpenHelper.instance(context);
		mLock = mDBOpenHelper.getLock();
	}

	public static synchronized CallAndSmsDB instance(Context context) {
		if (mInstance == null) {
			mInstance = new CallAndSmsDB(context);
		}

		return mInstance;
	}

	protected static void createTable(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" + COL_ID + " integer primary key autoincrement, "
				+ COL_SMS_IN + " INTEGER, " + COL_SMS_OUT + " INTEGER, " + COL_CALL_IN + " INTEGER, " + COL_CALL_OUT
				+ " INTEGER, " + COL_STATUS + " INTEGER, " + COL_DATE + " TEXT) ");
	}

	// 更新某天的短信接收数
	public boolean updateSmsIn(String date) {
		if (!exits(date)) {
			insert(date);
		}

		return updateColumn(date, COL_SMS_IN);
	}

	// 更新某天的短信发送数
	public boolean updateSmsOut(String date) {
		if (!exits(date)) {
			insert(date);
		}

		return updateColumn(date, COL_SMS_OUT);
	}

	// 更新某天的接听电话数
	public boolean updateCallIn(String date) {
		if (!exits(date)) {
			insert(date);
		}

		return updateColumn(date, COL_CALL_IN);
	}

	// 更新某天的拨打电话数
	public boolean updateCallOut(String date) {
		if (!exits(date)) {
			insert(date);
		}

		return updateColumn(date, COL_CALL_OUT);
	}

	// 判断某天数据是否存在
	public boolean exits(String date) {
		SQLiteDatabase db = null;
		Cursor cur = null;
		try {
			mLock.lock();
			db = mDBOpenHelper.getReadableDatabase();

			String selection = COL_DATE + "=?";
			String[] selectionArgs = new String[] { date };
			cur = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, null);

			return cur.moveToFirst();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			if (db != null) {
				db.close();
			}
			mLock.unlock();
		}

		return false;
	}

	// 插入一条数据
	public boolean insert(String date) {
		SQLiteDatabase db = null;
		try {
			mLock.lock();
			db = mDBOpenHelper.getWritableDatabase();

			ContentValues values = new ContentValues();
			values.put(COL_SMS_IN, 0);
			values.put(COL_SMS_OUT, 0);
			values.put(COL_CALL_IN, 0);
			values.put(COL_CALL_OUT, 0);
			values.put(COL_STATUS, 0);
			values.put(COL_DATE, date);
			long ret = db.insert(TABLE_NAME, null, values);

			return ret != -1;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}

			mLock.unlock();
		}
		return false;
	}

	// 更新某一天的某一列
	public boolean updateColumn(String date, String columnNane) {
		SQLiteDatabase db = null;
		Cursor cur = null;
		try {
			mLock.lock();
			db = mDBOpenHelper.getWritableDatabase();
			String[] columns = new String[] { columnNane };
			String selection = COL_DATE + "=?";
			String[] selectionArgs = new String[] { date };
			cur = db.query(TABLE_NAME, columns, selection, selectionArgs, null, null, null);

			int count = 0;
			if (cur.moveToFirst()) {
				count = cur.getInt(0);
			} else {
				return false;
			}
			count++;

			ContentValues values = new ContentValues();
			values.put(columnNane, count);
			String whereClause = COL_DATE + "=?";
			String[] whereArgs = new String[] { date };

			int ret = db.update(TABLE_NAME, values, whereClause, whereArgs);

			return ret != 0;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			if (db != null) {
				db.close();
			}
			mLock.unlock();
		}
		return false;
	}

	// 更新某天之前状态
	public boolean updateStatusByDate(String beforeDate) {
		SQLiteDatabase db = null;
		try {
			mLock.lock();
			db = mDBOpenHelper.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(COL_STATUS, 1);
			String whereClause = COL_DATE + "<? AND " + COL_STATUS + "=?";
			String[] whereArgs = new String[] { beforeDate, String.valueOf(0) };

			int ret = db.update(TABLE_NAME, values, whereClause, whereArgs);
			return ret != 0;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}
			mLock.unlock();
		}
		return false;
	}

	// 获取某天之前的日期
	public ArrayList<String> queryDateBeforeDate(String beforeDate) {
		ArrayList<String> ret = new ArrayList<String>(); // 避免返回为null，减少判断
		SQLiteDatabase db = null;
		Cursor cur = null;
		try {
			mLock.lock();
			db = mDBOpenHelper.getReadableDatabase();
			String[] columns = new String[] { COL_DATE };
			String selection = COL_DATE + "<?";
			String[] selectionArgs = new String[] { beforeDate };

			cur = db.query(true, TABLE_NAME, columns, selection, selectionArgs, null, null, null, null);
			for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
				ret.add(cur.getString(cur.getColumnIndex(COL_DATE)));
			}
			return ret;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			if (db != null) {
				db.close();
			}
			mLock.unlock();
		}

		return ret;
	}

	// 获取某天的数据
	public CallAndSmsInfo queryByDate(String date) {
		SQLiteDatabase db = null;
		Cursor cur = null;
		try {
			mLock.lock();
			db = mDBOpenHelper.getReadableDatabase();
			String selection = COL_DATE + "=?";
			String[] selectionArgs = new String[] { date };

			cur = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, null);
			if (cur.moveToFirst()) {
				CallAndSmsInfo info = new CallAndSmsInfo();
				info.setSmsIn(cur.getInt(cur.getColumnIndex(COL_SMS_IN)));
				info.setSmsOut(cur.getInt(cur.getColumnIndex(COL_SMS_OUT)));
				info.setCallIn(cur.getInt(cur.getColumnIndex(COL_CALL_IN)));
				info.setCallOut(cur.getInt(cur.getColumnIndex(COL_CALL_OUT)));
				info.setStatus(cur.getInt(cur.getColumnIndex(COL_STATUS)));
				info.setDate(cur.getString(cur.getColumnIndex(COL_DATE)));
				return info;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			if (db != null) {
				db.close();
			}
			mLock.unlock();
		}

		return null;
	}

	// 获取某天之前的数据
	public ArrayList<CallAndSmsInfo> queryAllBeofreDate(String beforeDate) {
		SQLiteDatabase db = null;
		Cursor cur = null;
		try {
			mLock.lock();
			db = mDBOpenHelper.getReadableDatabase();
			String selection = COL_DATE + "<?";
			String[] selectionArgs = new String[] { beforeDate };

			cur = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, null);
			ArrayList<CallAndSmsInfo> ret = new ArrayList<CallAndSmsInfo>();
			for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
				CallAndSmsInfo info = new CallAndSmsInfo();
				info.setSmsIn(cur.getInt(cur.getColumnIndex(COL_SMS_IN)));
				info.setSmsOut(cur.getInt(cur.getColumnIndex(COL_SMS_OUT)));
				info.setCallIn(cur.getInt(cur.getColumnIndex(COL_CALL_IN)));
				info.setCallOut(cur.getInt(cur.getColumnIndex(COL_CALL_OUT)));
				info.setStatus(cur.getInt(cur.getColumnIndex(COL_STATUS)));
				info.setDate(cur.getString(cur.getColumnIndex(COL_DATE)));
				ret.add(info);
			}
			return ret;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			if (db != null) {
				db.close();
			}
			mLock.unlock();
		}

		return null;
	}

	// 删除某天之前的数据
	public boolean delAllBeforeDate(String beforeDate) {
		SQLiteDatabase db = null;
		try {
			mLock.lock();
			db = mDBOpenHelper.getReadableDatabase();
			String whereClause = COL_DATE + "<?";
			String[] whereArgs = new String[] { beforeDate };
			db.delete(TABLE_NAME, whereClause, whereArgs);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}
			mLock.unlock();
		}
		return false;
	}
}
